*file to process attendance and assignment data from the chicago police department and  
 
 
**Housekeeping
local ver_suff _9_21

 
clear all
cap log close
if "`c(username)'"=="taehokim" |"`c(username)'"=="Taeho Kim"{ 
	cd "/Users/`c(username)'/Dropbox (Penn Law)/misconduct/do_files`ver_suff'"
} 
else{
	cd "/Users/`c(username)'/Dropbox/misconduct/do_files`ver_suff'"
}

log using ../logs`ver_suff'/aa_data.log, text replace
set more off
 
**Process AA data to yearly
**AA data post-processing (too many observations/fields to carry along with MID process)
*******************
*MERGE MIDS TO FIELD DATA
*******************

forval i = 1/26{ 
use ../FOIA/Attendance/A_A_`i', clear 
destring yearofbirth, replace

rename yearofbirth birthyear
drop if Sworn == "N"

//Match to MID not matched include non-sworn officers like detention aides and cross guards
merge m:1 officername birthyear appointed_date using ../processed_data`ver_suff'/FOIA_unified/AA, keep(3) nogen

gen year = year(AADate)
gen mo = month(AADate)
gen day = day(AADate) 
 
 
gen modayyr = mdy(mo,day,year)
format %d modayyr

save ../processed_data`ver_suff'/attendance/fieldaamid_`i', replace 
}

*******************
*SPLIT FIELD DATA BY YEAR
*******************

//from 2008 to 2020: each year spans two files

forval i = 1(2)26{
 
local n = `i'+1
use ../processed_data`ver_suff'/attendance/fieldaamid_`i', clear
append using ../processed_data`ver_suff'/attendance/fieldaamid_`n', force

local year = ceil(`i'/2)+2007
display `year'
keep if year == `year'
duplicates drop 

gen app_year = year(appointed_date)
gen app_mo = month(appointed_date)
gen app_yearmo = ym(app_year, app_mo)
format %tm app_yearmo
tab app_yearmo

drop if app_year>year //data error if the data says the officers were there before joining the organization

tab mo  //starting from 2008 September I see complete data
save ../processed_data`ver_suff'/attendance/aamid_`year', replace
}


**Process yearly AA data
forval year = 2008/2020{
 
use ../processed_data`ver_suff'/attendance/aamid_`year', clear 

if `year' ==2008{
keep if mo>=9
}



	*****************************
	*CLEAN UP BEAT 
	*****************************


// cleaning up some of the beat numbers
rename BeatNo beat_no 
rename Unit cpd_unit_no
destring cpd_unit_no, replace
replace beat_no= substr(beat_no,2,8) if substr(beat_no,1,1) == "`"
replace beat_no = "0" + substr(beat_no,2,8) if substr(beat_no,1,1) == "X" & substr(beat_no,2,1) != "0"
replace beat_no = "0" + beat_no if strlen(beat_no) == 3 & regexm(beat_no,"[0-9]") == 1 & substr(beat_no,1,1) ~= "0"
replace beat_no = substr(beat_no,2,8) if substr(beat_no,1,1)== "."
tostring cpd_unit_no, gen(cpd_string)
replace beat_no = substr(beat_no,3,8) if cpd_string == substr(beat_no,1,2) & cpd_string == substr(beat_no,3,2) & regexm(substr(beat_no,6,1),"[0-9]") == 1
replace beat_no = substr(beat_no,2,8) if cpd_string == substr(beat_no,1,2) & cpd_string == substr(beat_no,3,2) & regexm(substr(beat_no,5,1),"[0-9]") == 1
replace beat_no = "0"+ beat_no if length(beat_no) ==4 &regexm(substr(beat_no,4,1),"[0-9]") == 0 & substr(beat_no,1,1) ~= "0"
 
gen sector_no = substr(beat_no,3,1)
 
 
gen desk = (CarNo == ""|regexm(CarNo,"[0-9]") == 0) & Present == "Y"

gen attend = Present == "Y"

gen tact = sector_no == "6"& cpd_unit_no <=25 & Present == "Y"

gen district = cpd_unit_no<=25

gen special = cpd_unit_no== 79| (cpd_unit_no>=180 & cpd_unit_no<=213)|cpd_unit_no== 253|(cpd_unit_no>=311 & cpd_unit_no<=315)|cpd_unit_no== 353|cpd_unit_no== 376|cpd_unit_no== 393|(cpd_unit_no>=605 & cpd_unit_no<=766) & Present == "Y"
 
gen yearmo = ym(year, mo)
format %tm yearmo

keep if attend == 1
**Total force and injury incidents within a given yearmonth for a given officer
collapse (sum) attend (mean) desk (mean) tact (mean) special (mean) district, by(mid yearmo)


tempfile aa_`year'
save `aa_`year''
}
 
use `aa_2008', clear

forval i = 2009/2020{
append using `aa_`i''
}
 
 
**Save unified panel of outcomes
duplicates drop
save ../processed_data`ver_suff'/aa_panel, replace
 
clear
log close


